﻿/*
    Copyright (c) 2012, Matías López
    All rights reserved.

    Redistribution and use in source and binary forms, with or without modification, are permitted 
    provided that the following conditions are met:
        *   Redistributions of source code must retain the above copyright notice, this list of 
            conditions and the following disclaimer.
        *   Redistributions in binary form must reproduce the above copyright notice, this list of 
            conditions and the following disclaimer in the documentation and/or other materials 
            provided with the distribution.
        *   Neither the name of Matías López nor the names of its contributors may be used to 
            endorse or promote products derived from this software without specific prior written 
            permission.
    
    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
    IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
    AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
    CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
    CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR 
    SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 
    THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR 
    OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
    POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections.Generic;

namespace mana.Data.Sql
{
    public class Transaccion : IDisposable
    {
        private string _connectionString;
        private bool _isConnectionOpen;
        private int _commandTimeout;
        private SqlConnection objConection;
        private SqlTransaction objTransaccion;
        private SqlCommand objCommand;
        private SqlDataReader objDataReader;
        private SqlDataAdapter objDataAdapter;

        public Transaccion()
            :this(null)
        { }

        public Transaccion(string connectionString)
        {
            _connectionString = connectionString;
            _isConnectionOpen = false;
            _commandTimeout = 30;
        }

        public string ConnectionString
        {
            get { return _connectionString; }
        }

        public int CommandTimeout
        {
            get { return _commandTimeout; }
            set { _commandTimeout = value; }
        }

        public IsolationLevel IsolationLevel
        {
            get 
            {
                if (!_isConnectionOpen)
                    return System.Data.IsolationLevel.Unspecified;

                return objCommand.Transaction.IsolationLevel; 
            }
        }

        /// <summary>
        /// Starts a Transaction using IsolationLevel = ReadCommited.
        /// </summary>
        public void Begin()
        {
            Begin(IsolationLevel.ReadCommitted);
        }

        public void Begin(IsolationLevel isolationLevel)
        {
            objConection = new SqlConnection();

            if (string.IsNullOrWhiteSpace(_connectionString))
            {
                int cant = ConfigurationManager.ConnectionStrings.Count;
                if (cant == 0)
                    throw new Exception("No connection string configured.");

                ConnectionStringSettings connString = ConfigurationManager.ConnectionStrings[cant - 1];
                _connectionString = connString.ConnectionString;
            }
            else
            {
                string nombreConnStringPrefijo = "name=";
                if (_connectionString.Contains(nombreConnStringPrefijo))
                {
                    string connStringName = _connectionString.Replace(nombreConnStringPrefijo, "");

                    ConnectionStringSettings connString = ConfigurationManager.ConnectionStrings[connStringName];
                    if (connString == null)
                        throw new Exception("The connection string '" + connStringName + "' is not configured.");

                    _connectionString = connString.ConnectionString;
                }
            }

            objConection.ConnectionString = _connectionString;
            objConection.Open();
            
            objTransaccion = objConection.BeginTransaction(isolationLevel);

            objCommand = objConection.CreateCommand();
            objCommand.Transaction = objTransaccion;

            _isConnectionOpen = true;
        }

        public DataReader ExecuteStoredProcedure(string spName)
        {
            return ExecuteStoredProcedure(spName, null);
        }

        public DataReader ExecuteStoredProcedure(string spName, IEnumerable<SqlParameter> parameters)
        {
            if (!_isConnectionOpen)
                Begin();

            this.SetParameters(parameters);
            this.SetCommandStoredProcedure(spName);

            objDataReader = objCommand.ExecuteReader();
            return new DataReader(objDataReader);
        }

        public int ExecuteNonQuery(string instruccionSQL)
        {
            return ExecuteNonQuery(instruccionSQL, null);
        }

        public int ExecuteNonQuery(string cmd, IEnumerable<SqlParameter> parameters)
        {
            if (!_isConnectionOpen)
                Begin();

            this.SetParameters(parameters);
            this.SetCommandText(cmd);

            return objCommand.ExecuteNonQuery();
        }

        public DataReader ExecuteReader(string cmd)
        {
            return ExecuteReader(cmd, null);
        }

        public DataReader ExecuteReader(string cmd, IEnumerable<SqlParameter> parameters)
        {
            if (!_isConnectionOpen)
                Begin();

            this.SetParameters(parameters);
            this.SetCommandText(cmd);

            objDataReader = objCommand.ExecuteReader();

            return new DataReader(objDataReader);
        }

        public object ExecuteScalar(string cmd)
        {
            return ExecuteScalar(cmd, null);
        }

        public object ExecuteScalar(string cmd, IEnumerable<SqlParameter> parameters)
        {
            if (!_isConnectionOpen)
                Begin();

            this.SetParameters(parameters);
            this.SetCommandText(cmd);
                        
            return objCommand.ExecuteScalar();
        }

        public DataTable ReadTable(string cmd)
        {
            return ReadTable(cmd, null);
        }

        public DataTable ReadTable(string cmd, IEnumerable<SqlParameter> parameters)
        {
            if (!_isConnectionOpen)
                Begin();

            this.SetParameters(parameters);
            this.SetCommandText(cmd);
            
            var data = new DataTable("data");
            if (objDataAdapter == null)
                objDataAdapter = new SqlDataAdapter(objCommand);

            objDataAdapter.Fill(data);
            return data;
        }

        /// <summary>
        /// Returns the last generated identity from the database (@@IDENTITY).
        /// </summary>
        /// <returns></returns>
        public T GetGeneratedIdentity<T>() where T : struct
        {
            return (T)Convert.ChangeType(ExecuteScalar("SELECT @@IDENTITY"), typeof(T));
        }

        /// <summary>
        /// Returns the last-used timestamp value of the database (@@DBTS).
        /// </summary>
        /// <returns></returns>
        public byte[] GetDbTimestamp()
        {
            return (byte[])ExecuteScalar("SELECT @@DBTS");
        }

        private void SetCommandText(string commandText)
        {
            objCommand.CommandType = CommandType.Text;
            objCommand.CommandText = commandText;
            objCommand.CommandTimeout = CommandTimeout;
        }

        private void SetCommandStoredProcedure(string storedProcedureName)
        {
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.CommandText = storedProcedureName;
            objCommand.CommandTimeout = CommandTimeout;
        }
        
        private void SetParameters(IEnumerable<SqlParameter> parameters)
        {
            this.ClearParameters();
            
            if (parameters == null)
                return;

            foreach (SqlParameter parametro in parameters)
            {
                if (parametro.Value == null)
                    parametro.Value = DBNull.Value;

                objCommand.Parameters.Add(parametro);
            }
        }

        private void ClearParameters()
        {
            if (objCommand == null)
                return;

            objCommand.Parameters.Clear();
        }

        public void Commit()
        {
            if (!_isConnectionOpen)
                return;

            this.CloseDataReader();

            objTransaccion.Commit();
            objConection.Close();
            
            this.Dispose();
        }

        public void RollBack()
        {
            if (!_isConnectionOpen)
                return;

            this.CloseDataReader();

            objTransaccion.Rollback();
            objConection.Close();

            this.Dispose();
        }

        public void Dispose()
        {
            if (!_isConnectionOpen)
                return;

            this.CloseDataReader();

            objCommand.Dispose();
            objTransaccion.Dispose();
            objConection.Dispose();
        }

        private void CloseDataReader()
        {
            if (!_isConnectionOpen)
                return;

            if (objDataReader != null)
            {
                objCommand.Cancel();
                objDataReader.Close();
            }
        }
    }
}
